package com.hnkywl.sts.dao.jsgl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.entity.cggl.Cgdmx;
import com.hnkywl.sts.entity.cggl.Glfy;
import com.hnkywl.sts.entity.cggl.Jsd;
import com.hnkywl.sts.entity.xsgl.Xsmx;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.Criteria;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-4-30
 * Time: 上午9:59
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class FpDao extends SimpleHibernateTemplate<Jsd, Long> {
    public Page<Jsd> findfpList(Integer type, Integer zt, String jsdh, Date sqrqStart, Date sqrqEnd, Long glqyId, String glqyQc, Long gsId, String gsJc, Long fpzlId, int pageNo, int pageSize, Long ywgsID) {
        String hql;
        String szt = "2,4,5,6";
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Jsd> page = new Page<Jsd>(pageNo, pageSize, 0);
        //==============条件拼接===============
        if (jsdh != null && !jsdh.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" jsdh like ? ");
            param.add("%" + jsdh.trim() + "%");
        }
        if (sqrqStart != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" sqrq >= ?");
            param.add(sqrqStart);
        }
        if (sqrqEnd != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" sqrq <= ?");
            param.add(sqrqEnd);
        }
        if (glqyId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" glqy.id = ? ");
            param.add(glqyId);
        }
        if (zt != null) {
            szt = "" + zt + "";
        }
        if (glqyQc != null && !"".equals(glqyQc)) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" glqy.qc like ? ");
            param.add("%" + glqyQc.trim() + "%");
        }
        if (gsId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" gs.id = ? ");
            param.add(gsId);
        }

        if (gsJc != null && !"".equals(gsJc)) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" gs.jc like ? ");
            param.add("%" + gsJc.trim() + "%");
        }
        if (fpzlId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" fpzl = ? ");
            param.add(fpzlId);
        }
        if (type != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" type = ? ");
            param.add(type);
        }

        if (condition.length() != 0)
            condition.append(" and ");
        condition.append(" zt in (" + szt + ") ");
        //==============记录数量===============
        hql = "select count(*) from Jsd where type in (1,2) " + RegionDao.createFpRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        Long c;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果=============== 销售结算和物流结算
        hql = "from Jsd where type in (1,2) " + RegionDao.createFpRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition + "order by zt asc";
        if (param.size() != 0)
            return find(page, hql, param.toArray());
        return find(page, hql);
    }

    public Page<Jsd> findfpdrList(Integer type, Integer fpdrZt, String jsdh, Date sqrqStart, Date sqrqEnd, Long glqyId, String glqyQc, Long gsId, String gsJc, Long fpzlId, int pageNo, int pageSize) {
        String hql;
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Jsd> page = new Page<Jsd>(pageNo, pageSize, 0);
        //==============条件拼接===============
        if (jsdh != null && !jsdh.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" jsdh like ? ");
            param.add("%" + jsdh.trim() + "%");
        }
        if (sqrqStart != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" sqrq >= ?");
            param.add(sqrqStart);
        }
        if (sqrqEnd != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" sqrq <= ?");
            param.add(sqrqEnd);
        }
        if (glqyId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" glqy.id = ? ");
            param.add(glqyId);
        }
        if (glqyQc != null && !"".equals(glqyQc)) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" glqy.qc like ? ");
            param.add("%" + glqyQc.trim() + "%");
        }
        if (gsId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" gs.id = ? ");
            param.add(gsId);
        }
        if (gsJc != null && !"".equals(gsJc)) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" gs.jc like ? ");
            param.add("%" + gsJc.trim() + "%");
        }
        if (fpzlId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" fpzl = ? ");
            param.add(fpzlId);
        }
        if (type != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" type = ? ");
            param.add(type);
        }
        if (condition.length() != 0)
            condition.append(" and ");
//        condition.append(" zt = ? ");
//        param.add(3);
        condition.append(" (type in (1,2) and zt = 3 or type=0 and zt=2) ");
//        if (condition.length() != 0)
//            condition.append(" and ");
//        condition.append(" shzt = ? ");
//        param.add(1);
        if (condition.length() != 0)
            condition.append(" and ");
        condition.append(" jsdhb.id is null ");

        if (fpdrZt != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            //status=0未导入
            //status=1导入成功
            //status=2导入失败
            //status=3删除成功
            //status=4删除失败
            condition.append(" ( (" + fpdrZt + "=0 and insertFlag=1) " +
                    "or (" + fpdrZt + "=1 and insertFlag=3 and deleteFlag!=2) " +
                    "or (" + fpdrZt + "=2 and insertFlag=2) " +
                    "or (" + fpdrZt + "=3 and deleteFlag=3 and insertFlag!=2) " +
                    "or (" + fpdrZt + "=4 and deleteFlag=2) ) ");
        }

        //==============记录数量===============
        hql = "select count(*) from Jsd where 1=1 " +
                " and (createUser.id is not null or (createUser.id is null and gs.id  in (select id from Nbgs ))) "
                + RegionDao.createFpRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        Long c;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Jsd where 1=1 " +
                " and (createUser.id is not null or (createUser.id is null and gs.id   in (select id from Nbgs))) "
                + RegionDao.createFpRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition + "order by dateCreated desc";
        if (param.size() != 0)
            return find(page, hql, param.toArray());
        return find(page, hql);
    }

    public List<Jsd> findAllJsdByJsdhbId(Long jsdhbId) {
        return find("from Jsd where jsdhb.id = ? ", jsdhbId);
    }


    public Boolean qxshyz(Long fpId) {
        String sql1 = "select gs.id from t_biz_jsd jsd left join t_sys_gsjg gs on jsd.glqy_id = gs.id where jsd.id = ? and gs.deleted = 0";
        if (!existsSQLQuery(sql1, fpId)) {
            throw new RuntimeException("供应商或客户已删除！");
        }

        String sql2 = "select * from t_biz_jsd where jiuqi_insert_flag = 3 and id = ?";
        if (existsSQLQuery(sql2, fpId)) {
            throw new RuntimeException("发票已导入财务系统！");
        }
        return true;
    }

    public List<Map> fpCgjsdList(Long fpcgjsdId) {
        Criteria criteria = getSession().createCriteria(Cgdmx.class)
                .createAlias("gg", "gg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgjsd", "cgjsd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgd", "cgd", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("id").as("id"))
                                .add(Property.forName("cgd.sl").as("cgd.sl"))
                                .add(Property.forName("cgd.id").as("cgd.id"))
                                .add(Property.forName("cgd.cgdh").as("cgdh"))
                                .add(Property.forName("gg.id").as("wzgg.id"))
                                .add(Property.forName("gg.code").as("wzgg.code"))
                                .add(Property.forName("gg.name").as("wzgg.name"))
                                .add(Property.forName("gg.cz").as("wzgg.cz"))
                                .add(Property.forName("gg.ggxh").as("wzgg.ggxh"))
                                .add(Property.forName("gg.dw").as("wzgg.dw"))
                                .add(Property.forName("cd.id").as("cd.id"))
                                .add(Property.forName("cd.z").as("cd.z"))
                                .add(Property.forName("rksl").as("rksl"))
                                .add(Property.forName("sbsl").as("sbsl"))
                                .add(Property.forName("jzfs.id").as("jzfs.id"))
                                .add(Property.forName("jzfs.z").as("jzfs.z"))
                                .add(Property.forName("hsj").as("hsj"))
                                .add(Property.forName("bhsj").as("bhsj"))
                                .add(Property.forName("hse").as("hse"))
                                .add(Property.forName("bhse").as("bhse"))
                                .add(Property.forName("jsbhse").as("je"))
                                .add(Property.forName("jsse").as("se"))
                                .add(Property.forName("jsce").as("ce"))
                                .add(Property.forName("cgjsd.me").as("me"))
                                .add(Property.forName("cgjsd.ddfy").as("ddfy"))
                ).add(Restrictions.eq("cgjsd.id", fpcgjsdId))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return (List<Map>) criteria.list();
    }

    public List<Map> fpXsjsdList(Long fpxsjsdId) {
        Criteria criteria = getSession().createCriteria(Xsmx.class)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jtwz", "wzgg", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh.cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jzfs", "jzfs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsd", "xsd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsd.gc", "gc", CriteriaSpecification.LEFT_JOIN)
                .createAlias("xsjsd", "xsjsd", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("id").as("id"))
                                .add(Property.forName("xsd.id").as("xsd.id"))
                                .add(Property.forName("xsd.xsdh").as("xsdh"))
                                .add(Property.forName("gc.mc").as("gc.mc"))
                                .add(Property.forName("kh.id").as("kh.id"))
                                .add(Property.forName("kh.xsxt_xsdh").as("yyxsdh"))
                                .add(Property.forName("kh.carNo").as("carNo"))
                                .add(Property.forName("kh.bindNo").as("bindNo"))
                                .add(Property.forName("xscb").as("xscb"))
                                .add(Property.forName("ck").as("ck"))
                                .add(Property.forName("wzgg.id").as("wzgg.id"))
                                .add(Property.forName("wzgg.code").as("wzgg.code"))
                                .add(Property.forName("wzgg.name").as("wzgg.name"))
                                .add(Property.forName("wzgg.cz").as("wzgg.cz"))
                                .add(Property.forName("wzgg.ggxh").as("wzgg.ggxh"))
                                .add(Property.forName("wzgg.dw").as("wzgg.dw"))
                                .add(Property.forName("js").as("js"))
                                .add(Property.forName("ck_sbsl").as("ck_sbsl"))
                                .add(Property.forName("sbsl").as("sbsl"))
                                .add(Property.forName("jzfs.id").as("jzfs.id"))
                                .add(Property.forName("jzfs.z").as("jzfs.z"))
                                .add(Property.forName("ck_js").as("ck_js"))
                                .add(Property.forName("cd.id").as("cd.id"))
                                .add(Property.forName("cd.z").as("cd.z"))
                                .add(Property.forName("ck_sbsl").as("cksl"))
                                .add(Property.forName("hsj").as("hsj"))
                                .add(Property.forName("bhsj").as("bhsj"))
                                .add(Property.forName("kpbhsj").as("kpbhsj"))
                                .add(Property.forName("sjdj").as("sjdj"))
                                .add(Property.forName("sjghje").as("sjghje"))
                                .add(Property.forName("txx").as("txx"))
                                .add(Property.forName("dlf").as("dlf"))
                                .add(Property.forName("zjzyf").as("zjzyf"))
                                .add(Property.forName("xkyh").as("xkyh"))
                                .add(Property.forName("jsbhse").as("je"))
                                .add(Property.forName("jsse").as("se"))
                                .add(Property.forName("se").as("fpse"))
                                .add(Property.forName("xsjsd.ce").as("ce"))
                                .add(Property.forName("xsjsd.ddfy").as("ddfy"))
                ).add(Restrictions.eq("xsjsd.id", fpxsjsdId))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return (List<Map>) criteria.list();
    }

    public List<Map> fpWljsdList(Long fpwljsdId) {
        Criteria criteria = getSession().createCriteria(Glfy.class)
                .createAlias("sydw", "sydw", CriteriaSpecification.LEFT_JOIN)
                .createAlias("fylb", "fylb", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("sydw.id").as("sydw.id"))
                                .add(Property.forName("sydw.qc").as("sydw.qc"))
                                .add(Property.forName("fylb.id").as("fylb.id"))
                                .add(Property.forName("fylb.fykmjc").as("fylb.fykmjc"))
                                .add(Property.forName("jfsl").as("jfsl"))
                                .add(Property.forName("dj").as("dj"))
                                .add(Property.forName("je").as("je"))
                                .add(Property.forName("hdsl").as("hdsl"))
                                .add(Property.forName("hddj").as("hddj"))
                                .add(Property.forName("hdje").as("hdje"))
                                .add(Property.forName("kdse").as("kdse"))
                                .add(Property.forName("se").as("se"))
                                .add(Property.forName("me").as("me"))
                                .add(Property.forName("ce").as("ce"))
                                .add(Property.forName("jsje").as("jsje"))
                                .add(Property.forName("bz").as("bz"))
                ).add(Restrictions.eq("wljsd.id", fpwljsdId))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return (List<Map>) criteria.list();
    }
}
